﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;

namespace DusonFramework.DataAccess.Paging
{
    /// <summary>
    /// Max分页方法
    /// </summary>
    public class Paging_Max : PagingAbstract
    {
        /*
        declare @col int

        select top {PageSize * (PageIndex-1)+1} @col = [排序字段] 
        from [表名|视图名] 
        [ where 查询条件 ] 
        order by [排序字段] asc|desc
         
        select top PageSize 需要显示的字段 
        from [表名|视图名] 
        where [排序字段] >= @col
        [ and 查询条件 ]
        order by [排序字段] asc|desc
        */
        /// <summary>
        /// 适用于只有一个排序字段
        /// 建议：为排序字段设置聚集索引，以加速
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="fields">提取字段</param>
        /// <param name="filter">过滤条件</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="sortType">排序类型（0：升序，1：降序）</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页大小</param>
        /// <returns></returns>
        public override string GetPagingSQL(string table, string fields, string filter, string sortField, int sortType, int pageIndex, int pageSize)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            string sortType1 = string.Empty;
            string sortType2 = string.Empty;
            string operation = string.Empty;
            if (sortType == 1)
            {
                sortType1 = "asc";
                sortType2 = "desc";
                operation = ">=";
            }
            else
            {
                sortType1 = "desc";
                sortType2 = "asc";
                operation = "<=";
            }

            int tempPage;
            tempPage = pageSize * (pageIndex - 1) + 1;

            // 提取字段为空，默认显示全部字段
            if (string.IsNullOrEmpty(fields))
                fields = "*";

            string where = string.Empty;
            if (!string.IsNullOrEmpty(filter))
                where = string.Format(" where {0}", filter);

            // 第一页
            if (pageIndex == 1)
                sqlBuilder.Append(string.Format("select top {0} {1} from {2} {3} order by {4} {5}", pageSize, fields, table, where, sortField, sortType1));
            else
            {
                sqlBuilder.Append(string.Format(" select top {0} {1} from {2} where {3} {4}(", pageSize, fields, table, sortField, operation));
                sqlBuilder.Append(string.Format("    select top 1 {0} from (", sortField));
                sqlBuilder.Append(string.Format("        select top {0} {1} from {2} {3} order by {1} {4}) as tempTable", tempPage, sortField, table, where, sortType1));
                sqlBuilder.Append(string.Format("    order by {0} {1} )", sortField, sortType2));
                sqlBuilder.Append(string.Format(" order by {0} {1}", sortField, sortType1));
            }

            return sqlBuilder.ToString();
       }

    }

}
